package fr.kotton.myorganizer.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import fr.kotton.myorganizer.db.MyOrganizerDbContract.AppointmentEntry;
import fr.kotton.myorganizer.db.MyOrganizerDbContract.ContactEntry;
import fr.kotton.myorganizer.db.MyOrganizerDbContract.ContactGroupEntry;
import fr.kotton.myorganizer.db.MyOrganizerDbContract.ContactToContactGroupEntry;
import fr.kotton.myorganizer.db.MyOrganizerDbContract.EmailEntry;
import fr.kotton.myorganizer.db.MyOrganizerDbContract.PhoneNumberEntry;
import fr.kotton.myorganizer.db.MyOrganizerDbContract.TaskContentEntry;
import fr.kotton.myorganizer.db.MyOrganizerDbContract.TaskEntry;
import fr.kotton.myorganizer.db.MyOrganizerDbContract.TaskGroupEntry;
import fr.kotton.myorganizer.db.MyOrganizerDbContract.TaskToTaskGroupEntry;

public class MyOrganizerDbHelper extends SQLiteOpenHelper {

    // If you change the database schema, you must increment the database version.
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "MyOrganizer.db";
    private static final String TEXT_TYPE = " TEXT";
    private static final String INT_TYPE = " INTEGER";
    private static final String COMMA_SEP = ",";
    private static final String SQL_CREATE_CONTACTTOCONTACTGROUPENTRY =
            "CREATE TABLE " + ContactToContactGroupEntry.TABLE_NAME + " (" +
                    ContactToContactGroupEntry.COLUMN_NAME_CONTACTTOCONTACTGROUP_CONTACT_ID + INT_TYPE + COMMA_SEP +
                    ContactToContactGroupEntry.COLUMN_NAME_CONTACTTOCONTACTGROUP_GROUP_ID + INT_TYPE + COMMA_SEP +
                    "FOREIGN KEY (" + ContactToContactGroupEntry.COLUMN_NAME_CONTACTTOCONTACTGROUP_CONTACT_ID + ") REFERENCES " + ContactEntry.TABLE_NAME + "(" + ContactEntry.COLUMN_NAME_CONTACT_ID + ")" + COMMA_SEP +
                    "FOREIGN KEY (" + ContactToContactGroupEntry.COLUMN_NAME_CONTACTTOCONTACTGROUP_GROUP_ID + ") REFERENCES " + ContactGroupEntry.TABLE_NAME + "(" + ContactGroupEntry.COLUMN_NAME_CONTACTGROUP_ID + ")" +
                    ")";
    private static final String SQL_CREATE_TASKTOTASKGROUPENTRY =
            "CREATE TABLE " + TaskToTaskGroupEntry.TABLE_NAME + " (" +
                    TaskToTaskGroupEntry.COLUMN_NAME_TASKTOTASKGROUP_TASK_ID + INT_TYPE + COMMA_SEP +
                    TaskToTaskGroupEntry.COLUMN_NAME_TASKTOTASKGROUP_TASKGROUP_ID + INT_TYPE + COMMA_SEP +
                    "FOREIGN KEY (" + TaskToTaskGroupEntry.COLUMN_NAME_TASKTOTASKGROUP_TASK_ID + ") REFERENCES " + TaskEntry.TABLE_NAME + "(" + TaskEntry.COLUMN_NAME_TASK_ID + ")" + COMMA_SEP +
                    "FOREIGN KEY (" + TaskToTaskGroupEntry.COLUMN_NAME_TASKTOTASKGROUP_TASKGROUP_ID + ") REFERENCES " + TaskGroupEntry.TABLE_NAME + "(" + TaskGroupEntry.COLUMN_NAME_TASKGROUP_ID + ")" +
                    ")";
    private static final String SQL_CREATE_APPOINTMENTTOCONTACTENTRY =
            "CREATE TABLE " + MyOrganizerDbContract.AppointmentToContactEntry.TABLE_NAME + " (" +
                    MyOrganizerDbContract.AppointmentToContactEntry.COLUMN_NAME_APPOINTMENTTOCONTACT_CONTACT_ID + INT_TYPE + COMMA_SEP +
                    MyOrganizerDbContract.AppointmentToContactEntry.COLUMN_NAME_APPOINTMENTTOCONTACT_APPOINTMENT_ID + INT_TYPE + COMMA_SEP +
                    "FOREIGN KEY (" + MyOrganizerDbContract.AppointmentToContactEntry.COLUMN_NAME_APPOINTMENTTOCONTACT_CONTACT_ID + ") REFERENCES " + ContactEntry.TABLE_NAME + "(" + ContactEntry.COLUMN_NAME_CONTACT_ID + ")" + COMMA_SEP +
                    "FOREIGN KEY (" + MyOrganizerDbContract.AppointmentToContactEntry.COLUMN_NAME_APPOINTMENTTOCONTACT_APPOINTMENT_ID + ") REFERENCES " + AppointmentEntry.TABLE_NAME + "(" + AppointmentEntry.COLUMN_NAME_APPOINTMENT_ID + ")" +
                    ")";
    private static final String END_SEP = ";";
    private static final String SQL_CREATE_TASKENTRY =
            "CREATE TABLE " + TaskEntry.TABLE_NAME + " (" +
                    TaskEntry.COLUMN_NAME_TASK_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    TaskEntry.COLUMN_NAME_TASK_TITLE + TEXT_TYPE + COMMA_SEP +
                    TaskEntry.COLUMN_NAME_TASK_ENDDATE + INT_TYPE + COMMA_SEP +
                    TaskEntry.COLUMN_NAME_TASK_FREQUENCYUNIT + TEXT_TYPE + COMMA_SEP +
                    TaskEntry.COLUMN_NAME_TASK_REMINDERDELAY + INT_TYPE + COMMA_SEP +
                    TaskEntry.COLUMN_NAME_TASK_CONTENTTYPE + TEXT_TYPE +
                    ")" + END_SEP;
    private static final String SQL_CREATE_APPOINTMENTENTRY =
            "CREATE TABLE " + AppointmentEntry.TABLE_NAME + " (" +
                    AppointmentEntry.COLUMN_NAME_APPOINTMENT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    AppointmentEntry.COLUMN_NAME_APPOINTMENT_TASK_ID + INT_TYPE + COMMA_SEP +
                    AppointmentEntry.COLUMN_NAME_APPOINTMENT_TYPE + TEXT_TYPE + COMMA_SEP +
                    AppointmentEntry.COLUMN_NAME_APPOINTMENT_STARTDATE + INT_TYPE + COMMA_SEP +
                    AppointmentEntry.COLUMN_NAME_APPOINTMENT_ADDRESS + TEXT_TYPE + COMMA_SEP +
                    "FOREIGN KEY (" + AppointmentEntry.COLUMN_NAME_APPOINTMENT_TASK_ID + ") REFERENCES " + TaskEntry.TABLE_NAME + "(" + TaskEntry.COLUMN_NAME_TASK_ID + ")" +
                    ")" + END_SEP;
    private static final String SQL_CREATE_TASKCONTENTENTRY =
            "CREATE TABLE " + TaskContentEntry.TABLE_NAME + " (" +
                    TaskContentEntry.COLUMN_NAME_TASKCONTENT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    TaskContentEntry.COLUMN_NAME_TASKCONTENT_TASK_ID + INT_TYPE + COMMA_SEP +
                    TaskContentEntry.COLUMN_NAME_TASKCONTENT_CHECK + TEXT_TYPE + COMMA_SEP +
                    TaskContentEntry.COLUMN_NAME_TASKCONTENT_CONTENT + TEXT_TYPE + COMMA_SEP +
                    "FOREIGN KEY (" + TaskContentEntry.COLUMN_NAME_TASKCONTENT_TASK_ID + ") REFERENCES " + TaskEntry.TABLE_NAME + "(" + TaskEntry.COLUMN_NAME_TASK_ID + ")" +
                    ")" + END_SEP;
    private static final String SQL_CREATE_CONTACTENTRY =
            "CREATE TABLE " + ContactEntry.TABLE_NAME + " (" +
                    ContactEntry.COLUMN_NAME_CONTACT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    ContactEntry.COLUMN_NAME_CONTACT_FIRSTNAME + TEXT_TYPE + COMMA_SEP +
                    ContactEntry.COLUMN_NAME_CONTACT_LASTNAME + TEXT_TYPE +
                    ")" + END_SEP;
    private static final String SQL_CREATE_PHONENUMBERENTRY =
            "CREATE TABLE " + PhoneNumberEntry.TABLE_NAME + " (" +
                    PhoneNumberEntry.COLUMN_NAME_PHONENUMBER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    PhoneNumberEntry.COLUMN_NAME_PHONENUMBER_CONTACT_ID + INT_TYPE + COMMA_SEP +
                    PhoneNumberEntry.COLUMN_NAME_PHONENUMBER_TYPE + TEXT_TYPE + COMMA_SEP +
                    PhoneNumberEntry.COLUMN_NAME_PHONENUMBER_NUMBER + TEXT_TYPE + COMMA_SEP +
                    "FOREIGN KEY (" + PhoneNumberEntry.COLUMN_NAME_PHONENUMBER_CONTACT_ID + ") REFERENCES " + ContactEntry.TABLE_NAME + "(" + ContactEntry.COLUMN_NAME_CONTACT_ID + ")" +
                    ")" + END_SEP;
    private static final String SQL_CREATE_EMAILENTRY =
            "CREATE TABLE " + EmailEntry.TABLE_NAME + " (" +
                    EmailEntry.COLUMN_NAME_EMAIL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    EmailEntry.COLUMN_NAME_EMAIL_CONTACT_ID + INT_TYPE + COMMA_SEP +
                    EmailEntry.COLUMN_NAME_EMAIL_TYPE + TEXT_TYPE + COMMA_SEP +
                    EmailEntry.COLUMN_NAME_EMAIL_EMAIL + TEXT_TYPE + COMMA_SEP +
                    "FOREIGN KEY (" + EmailEntry.COLUMN_NAME_EMAIL_CONTACT_ID + ") REFERENCES " + ContactEntry.TABLE_NAME + "(" + ContactEntry.COLUMN_NAME_CONTACT_ID + ")" +
                    ")" + END_SEP;
    private static final String SQL_CREATE_CONTACTGROUPENTRY =
            "CREATE TABLE " + ContactGroupEntry.TABLE_NAME + " (" +
                    ContactGroupEntry.COLUMN_NAME_CONTACTGROUP_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    ContactGroupEntry.COLUMN_NAME_CONTACTGROUP_NAME + TEXT_TYPE + COMMA_SEP +
                    ContactGroupEntry.COLUMN_NAME_CONTACTGROUP_COLOR + TEXT_TYPE +
                    ")" + END_SEP;
    private static final String SQL_CREATE_TASKGROUPENTRY =
            "CREATE TABLE " + TaskGroupEntry.TABLE_NAME + " (" +
                    TaskGroupEntry.COLUMN_NAME_TASKGROUP_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    TaskGroupEntry.COLUMN_NAME_TASKGROUP_NAME + TEXT_TYPE + COMMA_SEP +
                    TaskGroupEntry.COLUMN_NAME_TASKGROUP_COLOR + TEXT_TYPE +
                    ")" + END_SEP;
    private static final String SQL_DELETE_TASKENTRY =
            "DROP TABLE IF EXISTS " + TaskEntry.TABLE_NAME + END_SEP;
    private static final String SQL_DELETE_TASKCONTENTENTRY =
            "DROP TABLE IF EXISTS " + TaskContentEntry.TABLE_NAME + END_SEP;
    private static final String SQL_DELETE_CONTACTENTRY =
            "DROP TABLE IF EXISTS " + ContactEntry.TABLE_NAME + END_SEP;
    private static final String SQL_DELETE_PHONENUMBERENTRY =
            "DROP TABLE IF EXISTS " + PhoneNumberEntry.TABLE_NAME + END_SEP;
    private static final String SQL_DELETE_CONTACTGROUPENTRY =
            "DROP TABLE IF EXISTS " + ContactGroupEntry.TABLE_NAME + END_SEP;
    private static final String SQL_DELETE_TASKGROUPENTRY =
            "DROP TABLE IF EXISTS " + TaskGroupEntry.TABLE_NAME + END_SEP;
    private static final String SQL_DELETE_EMAILENTRY =
            "DROP TABLE IF EXISTS " + EmailEntry.TABLE_NAME + END_SEP;
    private static final String SQL_DELETE_APPOINTMENTTOCONTACTENTRY =
            "DROP TABLE IF EXISTS " + MyOrganizerDbContract.AppointmentToContactEntry.TABLE_NAME + END_SEP;
    private static final String SQL_DELETE_CONTACTTOCONTACTGROUPENTRY =
            "DROP TABLE IF EXISTS " + ContactToContactGroupEntry.TABLE_NAME + END_SEP;
    private static final String SQL_DELETE_TASKTOTASKGROUPENTRY =
            "DROP TABLE IF EXISTS " + TaskToTaskGroupEntry.TABLE_NAME + END_SEP;
    private static final String SQL_DELETE_APPOINTMENTENTRY =
            "DROP TABLE IF EXISTS " + AppointmentEntry.TABLE_NAME + END_SEP;

    public MyOrganizerDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_TASKENTRY);
        db.execSQL(SQL_CREATE_TASKCONTENTENTRY);
        db.execSQL(SQL_CREATE_APPOINTMENTENTRY);
        db.execSQL(SQL_CREATE_CONTACTENTRY);
        db.execSQL(SQL_CREATE_PHONENUMBERENTRY);
        db.execSQL(SQL_CREATE_EMAILENTRY);
        db.execSQL(SQL_CREATE_CONTACTGROUPENTRY);
        db.execSQL(SQL_CREATE_TASKGROUPENTRY);
        db.execSQL(SQL_CREATE_TASKTOTASKGROUPENTRY);
        db.execSQL(SQL_CREATE_CONTACTTOCONTACTGROUPENTRY);
        db.execSQL(SQL_CREATE_APPOINTMENTTOCONTACTENTRY);
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        db.execSQL(SQL_DELETE_CONTACTTOCONTACTGROUPENTRY);
        db.execSQL(SQL_DELETE_TASKTOTASKGROUPENTRY);
        db.execSQL(SQL_DELETE_APPOINTMENTTOCONTACTENTRY);
        db.execSQL(SQL_DELETE_PHONENUMBERENTRY);
        db.execSQL(SQL_DELETE_EMAILENTRY);
        db.execSQL(SQL_DELETE_CONTACTGROUPENTRY);
        db.execSQL(SQL_DELETE_TASKGROUPENTRY);
        db.execSQL(SQL_DELETE_CONTACTENTRY);
        db.execSQL(SQL_DELETE_TASKCONTENTENTRY);
        db.execSQL(SQL_DELETE_APPOINTMENTENTRY);
        db.execSQL(SQL_DELETE_TASKENTRY);
        onCreate(db);
    }

    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }

}
